package cn.fzhui.dao;

import cn.fzhui.model.Student;
import cn.fzhui.util.DBUtils;
import cn.fzhui.util.PageList;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

public class StudentDao {

    /**
     * 存储
     *
     * @return
     */
    public int store(Student student) throws SQLException {
        String sql = "INSERT INTO student VALUES(null, ?, ?, ?, ?, ?, ?, ?, ?)";
        Connection connection = DBUtils.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, student.getNumber());
        preparedStatement.setString(2, student.getName());
        preparedStatement.setString(3, student.getSex());
        preparedStatement.setString(4, student.getBirthday());
        preparedStatement.setString(5, student.getEmail());
        preparedStatement.setString(6, student.getRemark());
        preparedStatement.setString(7, student.getPassword());
        preparedStatement.setInt(8, student.getAge());

        // 执行SQL，返回结果
        return preparedStatement.executeUpdate();
    }

    /**
     * 通过ID，更新数据
     */
    public int update(Student student) throws SQLException {
        QueryRunner queryRunner = DBUtils.getQueryRunner();
        String sql = "UPDATE student SET number = ?, name = ?, sex = ?, birthday = ?, email = ?, remark = ?, password = ?, age = ? WHERE id = ?";
        Object[] params = {
                student.getNumber(),
                student.getName(),
                student.getSex(),
                student.getBirthday(),
                student.getEmail(),
                student.getRemark(),
                student.getPassword(),
                student.getAge(),
                student.getId(),
        };
        return queryRunner.update(sql, params);
    }

    /**
     * 分页列表
     *
     * @return
     */
    public PageList<Student> pageList(int currentPage, String searchType, String keyword) throws SQLException {
        PageList pageList = new PageList();
        pageList.setCurrentPage(currentPage);
        QueryRunner queryRunner = DBUtils.getQueryRunner();
        String sql = null;
        String countSql = null;

        // 检索关键字
        if (searchType != null && keyword != null) {
            sql = "SELECT * FROM student WHERE " + searchType + " LIKE '%" + keyword + "%' LIMIT ?, ?";
            countSql = "SELECT COUNT(*) FROM student WHERE " + searchType + " LIKE '%" + keyword + "%'";
        } else {
            sql = "SELECT * FROM student LIMIT ?, ?";
            countSql = "SELECT COUNT(*) FROM student";
        }

        Object[] params = {
                pageList.getStart(),
                pageList.getPerPage(),
        };

        // 列表数据
        pageList.setList(queryRunner.query(sql, new BeanListHandler<Student>(Student.class), params));

        // 统计数据
        Long count = queryRunner.query(countSql, new ScalarHandler<Long>());
        pageList.setTotalNumber(count);

        return pageList;
    }

    /**
     * 根据ID删除
     *
     * @param id
     * @return
     */
    public int delete(int id) throws SQLException {
        String sql = "DELETE FROM student WHERE id = ?";
        QueryRunner queryRunner = DBUtils.getQueryRunner();
        Object[] params = {
                id
        };
        return queryRunner.update(sql, params);
    }
}
